//-----------------------------------------------------------------------
// <copyright file="BulkOrderDAO.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------
	
using System;
using System.Data;
using MIMS.WRS.Entities;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MIMS.WRS.Data
{	
	public class BulkOrderDAO : BulkOrderDAOBase
	{
		public BulkOrderDAO()
		{
			
		}

		public virtual BulkOrder GetBulkOrderDetails(int bulkOrderID)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersGetDetails");
                
				dbCommandWrapper.AddInParameter("@BulkOrderID", DbType.Int32, bulkOrderID);
                
				BulkOrder bulkOrder =  null;
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					if (reader.Read())
					{
						bulkOrder = CreateBulkOrderFromReader(reader);

						if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable = (int)reader["QuantityAvailable"];							

						reader.Close();
					}
				}
				return bulkOrder;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrderException, ex);
			}
		}

		public virtual BulkOrderCollection GetBulkOrdersIDSHDReport(int conPointID, bool isUSUser, int idsSort, int quantity, int ownerId, int userId, int target, int territoryId)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersIDSHDReport");
                
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);				
				dbCommandWrapper.AddInParameter("@idsSort", DbType.Int32, idsSort);				
				dbCommandWrapper.AddInParameter("@quantity", DbType.Int32, quantity);				
				dbCommandWrapper.AddInParameter("@IsUSUser", DbType.Boolean, isUSUser);
				dbCommandWrapper.AddInParameter("@OwnerId", DbType.Int32, ownerId);	
				dbCommandWrapper.AddInParameter("@UserID", DbType.Int32, userId);	
				dbCommandWrapper.AddInParameter("@Target", DbType.Int32, target);	
				dbCommandWrapper.AddInParameter("@TerritoryID", DbType.Int32, territoryId);	
			
				BulkOrderCollection bulkOrderCollection = new BulkOrderCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					string preProductCode = "";	
					BulkOrder bulkOrder = null;
					while (reader.Read())
					{						
						string productCode = "";
						if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) productCode = (string)reader["ProductCode"];

						if (productCode != preProductCode) 
						{
							bulkOrder =  new BulkOrder();
							if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderID"))) bulkOrder.BulkOrderID = (int)reader["BulkOrderID"];
							if (!reader.IsDBNull(reader.GetOrdinal("OrderDate"))) bulkOrder.OrderDate = (DateTime)reader["OrderDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("Type"))) bulkOrder.Type = (byte)reader["Type"];
							if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) bulkOrder.ProductCode = (string)reader["ProductCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Description"))) bulkOrder.Description = (string)reader["Description"];
							if (!reader.IsDBNull(reader.GetOrdinal("Color"))) bulkOrder.Color = (string)reader["Color"];
							if (!reader.IsDBNull(reader.GetOrdinal("StartDate"))) bulkOrder.StartDate = (DateTime)reader["StartDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyOrdered"))) bulkOrder.TotalQtyOrdered = (int)reader["TotalQtyOrdered"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyCompleted"))) bulkOrder.TotalQtyCompleted = (int)reader["TotalQtyCompleted"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressBelow50P"))) bulkOrder.TotalQtyInProgressBelow50P = (int)reader["TotalQtyInProgressBelow50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressAbove50P"))) bulkOrder.TotalQtyInProgressAbove50P = (int)reader["TotalQtyInProgressAbove50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("EstimatedDate"))) bulkOrder.EstimatedDate = (DateTime)reader["EstimatedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedDate"))) bulkOrder.StockLastCheckedDate = (DateTime)reader["StockLastCheckedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedQty"))) bulkOrder.StockLastCheckedQty = (int)reader["StockLastCheckedQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("FOB"))) bulkOrder.FOB = (decimal)reader["FOB"];
							if (!reader.IsDBNull(reader.GetOrdinal("CompletedDate"))) bulkOrder.CompletedDate = (DateTime)reader["CompletedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("SupplierCode"))) bulkOrder.SupplierCode = (string)reader["SupplierCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Owner"))) bulkOrder.Owner = (int)reader["Owner"];
							if (!reader.IsDBNull(reader.GetOrdinal("Catalogue"))) bulkOrder.Catalogue = (string)reader["Catalogue"];
							if (!reader.IsDBNull(reader.GetOrdinal("Page"))) bulkOrder.Page = (string)reader["Page"];
							if (!reader.IsDBNull(reader.GetOrdinal("Pack"))) bulkOrder.Pack = (string)reader["Pack"];
							if (!reader.IsDBNull(reader.GetOrdinal("PackQty"))) bulkOrder.PackQty = (int)reader["PackQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("CatalogID"))) bulkOrder.CatalogID = (int)reader["CatalogID"];
							if (!reader.IsDBNull(reader.GetOrdinal("CatalogName"))) bulkOrder.CatalogName = (string)reader["CatalogName"];
							if (!reader.IsDBNull(reader.GetOrdinal("Target"))) bulkOrder.Target = (bool)reader["Target"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription1"))) bulkOrder.SalesDescription1 = (string)reader["SalesDescription1"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription2"))) bulkOrder.SalesDescription2 = (string)reader["SalesDescription2"];
							
							if (!reader.IsDBNull(reader.GetOrdinal("AvailableInUSA"))) bulkOrder.AvailableInUSA = (bool)reader["AvailableInUSA"];
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable = (int)reader["QuantityAvailable"];
							//if (!reader.IsDBNull(reader.GetOrdinal("AdjustedQuantity"))) bulkOrder.AdjustedQuantity = (int)reader["AdjustedQuantity"];
							if (!reader.IsDBNull(reader.GetOrdinal("NotStarted"))) bulkOrder.NotStarted = (int)reader["NotStarted"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQtyValue"))) bulkOrder.MinOrderQtyValue = (int)reader["MinOrderQtyValue"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQty"))) bulkOrder.MinOrderQty = (string)reader["MinOrderQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("TallPaletQty"))) bulkOrder.TallPaletQty = (string)reader["TallPaletQty"];

							if (!reader.IsDBNull(reader.GetOrdinal("CatPackDesc"))) bulkOrder.CatPackDesc = (string)reader["CatPackDesc"];
							if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackQty"))) bulkOrder.MUKTHGCatPackQty = (int)reader["MUKTHGCatPackQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackDesc"))) bulkOrder.MUKTHGCatPackDesc = (string)reader["MUKTHGCatPackDesc"];
							
							if (!reader.IsDBNull(reader.GetOrdinal("SpecialOfferDisc"))) bulkOrder.SpecialOfferDisc = (decimal)reader["SpecialOfferDisc"];
							if (!reader.IsDBNull(reader.GetOrdinal("IsShownInLGDCatalog"))) bulkOrder.IsShownInLGDCatalog = (bool)reader["IsShownInLGDCatalog"];							

							// calculate reserved quantity
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityReserved"))) bulkOrder.QuantityReserved = (int)reader["QuantityReserved"];
							
							if (!reader.IsDBNull(reader.GetOrdinal("HomeDelivered15Price"))) bulkOrder.HomeDelivered15Price = (decimal)reader["HomeDelivered15Price"];
							if (!reader.IsDBNull(reader.GetOrdinal("CurrencyCode"))) bulkOrder.CurrencyCode = (string)reader["CurrencyCode"];

							if (!reader.IsDBNull(reader.GetOrdinal("IDSSort"))) bulkOrder.IDSSort = (string)reader["IDSSort"];

							if (!reader.IsDBNull(reader.GetOrdinal("StockLocation"))) bulkOrder.StockLocation = (string)reader["StockLocation"];
							if (!reader.IsDBNull(reader.GetOrdinal("UnitWeight"))) bulkOrder.UnitWeight = (decimal)reader["UnitWeight"];

							if (!reader.IsDBNull(reader.GetOrdinal("UnitsPerOuter"))) bulkOrder.UnitsPerOuter = (int)reader["UnitsPerOuter"];
							if (!reader.IsDBNull(reader.GetOrdinal("UnitFOBPrice"))) bulkOrder.UnitFOBPrice = (decimal)reader["UnitFOBPrice"];

							bulkOrderCollection.Add(bulkOrder);
						}
						else 
						{
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable += (int)reader["QuantityAvailable"];							
							//if (!reader.IsDBNull(reader.GetOrdinal("AdjustedQuantity"))) bulkOrder.AdjustedQuantity += (int)reader["AdjustedQuantity"];
						}

						preProductCode = productCode;
						
					}
					reader.Close();
				}
                				
				return bulkOrderCollection;			
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual BulkOrderCollection GetBulkOrdersIDSReport(int conPointID, bool isUSUser, int idsSort, int quantity, int ownerId, int userId, int territoryId)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersIDSReport");
                
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);				
				dbCommandWrapper.AddInParameter("@idsSort", DbType.Int32, idsSort);				
				dbCommandWrapper.AddInParameter("@quantity", DbType.Int32, quantity);				
				dbCommandWrapper.AddInParameter("@IsUSUser", DbType.Boolean, isUSUser);
				dbCommandWrapper.AddInParameter("@OwnerId", DbType.Int32, ownerId);	
				dbCommandWrapper.AddInParameter("@UserID", DbType.Int32, userId);	
				dbCommandWrapper.AddInParameter("@territoryId", DbType.Int32, territoryId);	
			
				BulkOrderCollection bulkOrderCollection = new BulkOrderCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					string preProductCode = "";	
					BulkOrder bulkOrder = null;
					while (reader.Read())
					{						
						string productCode = "";
						if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) productCode = (string)reader["ProductCode"];

						if (productCode != preProductCode) 
						{
							bulkOrder =  new BulkOrder();
							if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderID"))) bulkOrder.BulkOrderID = (int)reader["BulkOrderID"];
							if (!reader.IsDBNull(reader.GetOrdinal("OrderDate"))) bulkOrder.OrderDate = (DateTime)reader["OrderDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("Type"))) bulkOrder.Type = (byte)reader["Type"];
							if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) bulkOrder.ProductCode = (string)reader["ProductCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Description"))) bulkOrder.Description = (string)reader["Description"];
							if (!reader.IsDBNull(reader.GetOrdinal("Color"))) bulkOrder.Color = (string)reader["Color"];
							if (!reader.IsDBNull(reader.GetOrdinal("StartDate"))) bulkOrder.StartDate = (DateTime)reader["StartDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyOrdered"))) bulkOrder.TotalQtyOrdered = (int)reader["TotalQtyOrdered"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyCompleted"))) bulkOrder.TotalQtyCompleted = (int)reader["TotalQtyCompleted"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressBelow50P"))) bulkOrder.TotalQtyInProgressBelow50P = (int)reader["TotalQtyInProgressBelow50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressAbove50P"))) bulkOrder.TotalQtyInProgressAbove50P = (int)reader["TotalQtyInProgressAbove50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("EstimatedDate"))) bulkOrder.EstimatedDate = (DateTime)reader["EstimatedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedDate"))) bulkOrder.StockLastCheckedDate = (DateTime)reader["StockLastCheckedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedQty"))) bulkOrder.StockLastCheckedQty = (int)reader["StockLastCheckedQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("FOB"))) bulkOrder.FOB = (decimal)reader["FOB"];
							if (!reader.IsDBNull(reader.GetOrdinal("CompletedDate"))) bulkOrder.CompletedDate = (DateTime)reader["CompletedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("SupplierCode"))) bulkOrder.SupplierCode = (string)reader["SupplierCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Owner"))) bulkOrder.Owner = (int)reader["Owner"];
							if (!reader.IsDBNull(reader.GetOrdinal("Catalogue"))) bulkOrder.Catalogue = (string)reader["Catalogue"];
							if (!reader.IsDBNull(reader.GetOrdinal("Page"))) bulkOrder.Page = (string)reader["Page"];
							if (!reader.IsDBNull(reader.GetOrdinal("Pack"))) bulkOrder.Pack = (string)reader["Pack"];
							if (!reader.IsDBNull(reader.GetOrdinal("PackQty"))) bulkOrder.PackQty = (int)reader["PackQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("CatalogID"))) bulkOrder.CatalogID = (int)reader["CatalogID"];
							if (!reader.IsDBNull(reader.GetOrdinal("CatalogName"))) bulkOrder.CatalogName = (string)reader["CatalogName"];
							if (!reader.IsDBNull(reader.GetOrdinal("Target"))) bulkOrder.Target = (bool)reader["Target"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription1"))) bulkOrder.SalesDescription1 = (string)reader["SalesDescription1"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription2"))) bulkOrder.SalesDescription2 = (string)reader["SalesDescription2"];
							
							if (!reader.IsDBNull(reader.GetOrdinal("AvailableInUSA"))) bulkOrder.AvailableInUSA = (bool)reader["AvailableInUSA"];
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable = (int)reader["QuantityAvailable"];
							//if (!reader.IsDBNull(reader.GetOrdinal("AdjustedQuantity"))) bulkOrder.AdjustedQuantity = (int)reader["AdjustedQuantity"];
							if (!reader.IsDBNull(reader.GetOrdinal("NotStarted"))) bulkOrder.NotStarted = (int)reader["NotStarted"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQtyValue"))) bulkOrder.MinOrderQtyValue = (int)reader["MinOrderQtyValue"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQty"))) bulkOrder.MinOrderQty = (string)reader["MinOrderQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("TallPaletQty"))) bulkOrder.TallPaletQty = (string)reader["TallPaletQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("UnitSettingID"))) bulkOrder.UnitSettingID = (int)reader["UnitSettingID"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinQtyVolume"))) bulkOrder.MinQtyVolume = (decimal)reader["MinQtyVolume"];

							if (!reader.IsDBNull(reader.GetOrdinal("CatPackDesc"))) bulkOrder.CatPackDesc = (string)reader["CatPackDesc"];
							if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackQty"))) bulkOrder.MUKTHGCatPackQty = (int)reader["MUKTHGCatPackQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackDesc"))) bulkOrder.MUKTHGCatPackDesc = (string)reader["MUKTHGCatPackDesc"];
							if (!reader.IsDBNull(reader.GetOrdinal("HomeDelivered15Price"))) bulkOrder.HomeDelivered15Price = (decimal)reader["HomeDelivered15Price"];
							if (!reader.IsDBNull(reader.GetOrdinal("SpecialOfferDisc"))) bulkOrder.SpecialOfferDisc = (decimal)reader["SpecialOfferDisc"];
							if (!reader.IsDBNull(reader.GetOrdinal("IsShownInLGDCatalog"))) bulkOrder.IsShownInLGDCatalog = (bool)reader["IsShownInLGDCatalog"];							

							if (!reader.IsDBNull(reader.GetOrdinal("IDSSort"))) bulkOrder.IDSSort = (string)reader["IDSSort"];

							// calculate reserved quantity
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityReserved"))) bulkOrder.QuantityReserved = (int)reader["QuantityReserved"];							
							
							if (!reader.IsDBNull(reader.GetOrdinal("StockLocation"))) bulkOrder.StockLocation = (string)reader["StockLocation"];
							if (!reader.IsDBNull(reader.GetOrdinal("UnitWeight"))) bulkOrder.UnitWeight = (decimal)reader["UnitWeight"];

							if (!reader.IsDBNull(reader.GetOrdinal("UnitsPerOuter"))) bulkOrder.UnitsPerOuter = (int)reader["UnitsPerOuter"];
							if (!reader.IsDBNull(reader.GetOrdinal("UnitFOBPrice"))) bulkOrder.UnitFOBPrice = (decimal)reader["UnitFOBPrice"];

							bulkOrderCollection.Add(bulkOrder);
						}
						else 
						{
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable += (int)reader["QuantityAvailable"];							
							//if (!reader.IsDBNull(reader.GetOrdinal("AdjustedQuantity"))) bulkOrder.AdjustedQuantity += (int)reader["AdjustedQuantity"];
						}

						preProductCode = productCode;
						
					}
					reader.Close();
				}
                				
				return bulkOrderCollection;			
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual BulkOrderCollection GetBulkOrdersSalesReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersSalesReport");
                
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);				
			
				BulkOrderCollection bulkOrderCollection = new BulkOrderCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					//string preProductCode = "";	
					BulkOrder bulkOrder = null;
					while (reader.Read())
					{						
//						string productCode = "";
//						if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) productCode = (string)reader["ProductCode"];
//
//						if (productCode != preProductCode) 
//						{
							bulkOrder =  new BulkOrder();
							if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderID"))) bulkOrder.BulkOrderID = (int)reader["BulkOrderID"];
							if (!reader.IsDBNull(reader.GetOrdinal("OrderDate"))) bulkOrder.OrderDate = (DateTime)reader["OrderDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("Type"))) bulkOrder.Type = (byte)reader["Type"];
							if (!reader.IsDBNull(reader.GetOrdinal("ProductCode"))) bulkOrder.ProductCode = (string)reader["ProductCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Description"))) bulkOrder.Description = (string)reader["Description"];
							if (!reader.IsDBNull(reader.GetOrdinal("Color"))) bulkOrder.Color = (string)reader["Color"];
							if (!reader.IsDBNull(reader.GetOrdinal("StartDate"))) bulkOrder.StartDate = (DateTime)reader["StartDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyOrdered"))) bulkOrder.TotalQtyOrdered = (int)reader["TotalQtyOrdered"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyCompleted"))) bulkOrder.TotalQtyCompleted = (int)reader["TotalQtyCompleted"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressBelow50P"))) bulkOrder.TotalQtyInProgressBelow50P = (int)reader["TotalQtyInProgressBelow50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("TotalQtyInProgressAbove50P"))) bulkOrder.TotalQtyInProgressAbove50P = (int)reader["TotalQtyInProgressAbove50P"];
							if (!reader.IsDBNull(reader.GetOrdinal("EstimatedDate"))) bulkOrder.EstimatedDate = (DateTime)reader["EstimatedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedDate"))) bulkOrder.StockLastCheckedDate = (DateTime)reader["StockLastCheckedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("StockLastCheckedQty"))) bulkOrder.StockLastCheckedQty = (int)reader["StockLastCheckedQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("FOB"))) bulkOrder.FOB = (decimal)reader["FOB"];
							if (!reader.IsDBNull(reader.GetOrdinal("CompletedDate"))) bulkOrder.CompletedDate = (DateTime)reader["CompletedDate"];
							if (!reader.IsDBNull(reader.GetOrdinal("SupplierCode"))) bulkOrder.SupplierCode = (string)reader["SupplierCode"];
							if (!reader.IsDBNull(reader.GetOrdinal("Owner"))) bulkOrder.Owner = (int)reader["Owner"];
							if (!reader.IsDBNull(reader.GetOrdinal("Catalogue"))) bulkOrder.Catalogue = (string)reader["Catalogue"];
							if (!reader.IsDBNull(reader.GetOrdinal("Page"))) bulkOrder.Page = (string)reader["Page"];
							if (!reader.IsDBNull(reader.GetOrdinal("Pack"))) bulkOrder.Pack = (string)reader["Pack"];
							if (!reader.IsDBNull(reader.GetOrdinal("PackQty"))) bulkOrder.PackQty = (int)reader["PackQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("CatalogID"))) bulkOrder.CatalogID = (int)reader["CatalogID"];
							if (!reader.IsDBNull(reader.GetOrdinal("Target"))) bulkOrder.Target = (bool)reader["Target"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription1"))) bulkOrder.SalesDescription1 = (string)reader["SalesDescription1"];
							if (!reader.IsDBNull(reader.GetOrdinal("SalesDescription2"))) bulkOrder.SalesDescription2 = (string)reader["SalesDescription2"];
							
							if (!reader.IsDBNull(reader.GetOrdinal("AvailableInUSA"))) bulkOrder.AvailableInUSA = (bool)reader["AvailableInUSA"];
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable = (int)reader["QuantityAvailable"];
							if (!reader.IsDBNull(reader.GetOrdinal("NotStarted"))) bulkOrder.NotStarted = (int)reader["NotStarted"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQtyValue"))) bulkOrder.MinOrderQtyValue = (int)reader["MinOrderQtyValue"];
							if (!reader.IsDBNull(reader.GetOrdinal("MinOrderQty"))) bulkOrder.MinOrderQty = (string)reader["MinOrderQty"];
							if (!reader.IsDBNull(reader.GetOrdinal("TallPaletQty"))) bulkOrder.TallPaletQty = (string)reader["TallPaletQty"];

						if (!reader.IsDBNull(reader.GetOrdinal("CatPackDesc"))) bulkOrder.CatPackDesc = (string)reader["CatPackDesc"];
						if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackQty"))) bulkOrder.MUKTHGCatPackQty = (int)reader["MUKTHGCatPackQty"];
						if (!reader.IsDBNull(reader.GetOrdinal("MUKTHGCatPackDesc"))) bulkOrder.MUKTHGCatPackDesc = (string)reader["MUKTHGCatPackDesc"];
						if (!reader.IsDBNull(reader.GetOrdinal("HomeDelivered15Price"))) bulkOrder.HomeDelivered15Price = (decimal)reader["HomeDelivered15Price"];
						if (!reader.IsDBNull(reader.GetOrdinal("SpecialOfferDisc"))) bulkOrder.SpecialOfferDisc = (decimal)reader["SpecialOfferDisc"];
						if (!reader.IsDBNull(reader.GetOrdinal("IsShownInLGDCatalog"))) bulkOrder.IsShownInLGDCatalog = (bool)reader["IsShownInLGDCatalog"];							

							// calculate reserved quantity
							if (!reader.IsDBNull(reader.GetOrdinal("QuantityReserved"))) bulkOrder.QuantityReserved = (int)reader["QuantityReserved"];
							
						if (!reader.IsDBNull(reader.GetOrdinal("IDSSort"))) bulkOrder.IDSSort = (string)reader["IDSSort"];

						if (!reader.IsDBNull(reader.GetOrdinal("UnitsPerOuter"))) bulkOrder.UnitsPerOuter = (int)reader["UnitsPerOuter"];
						if (!reader.IsDBNull(reader.GetOrdinal("UnitFOBPrice"))) bulkOrder.UnitFOBPrice = (decimal)reader["UnitFOBPrice"];


							bulkOrderCollection.Add(bulkOrder);
//						}
//						else 
//						{
//							if (!reader.IsDBNull(reader.GetOrdinal("QuantityAvailable"))) bulkOrder.QuantityAvailable += (int)reader["QuantityAvailable"];							
//						}
//
//						preProductCode = productCode;
//						
					}
					reader.Close();
				}
                				
				return bulkOrderCollection;			
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrdersAvailabilityReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersAvailabilityReport");            
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);	

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrdersProStatsReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersProStatsReport");      
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);	

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrdersCompletionDateSupplierReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersCompletionDateSupplierReport");      
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);	

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrdersCompletionDateOwnerReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersCompletionDateOwnerReport");      
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);	

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrdersOwnerBulkOrderReport(int conPointID, string supplier, int owner, int type, int target, int both, int qty, bool isUSUser)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersOwnerBulkOrderReport");      
				dbCommandWrapper.AddInParameter("@conPointID", DbType.Int32, conPointID);
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplier);
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@target", DbType.Int32, target);
				dbCommandWrapper.AddInParameter("@both", DbType.Int32, both);
				dbCommandWrapper.AddInParameter("@qty", DbType.Int32, qty);
				dbCommandWrapper.AddInParameter("@isUSUser", DbType.Boolean, isUSUser);	

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual IDataReader GetBulkOrders(BulkOrderColumns orderBy, OrderDirection orderDirection, int page, int pageSize)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersGetList");
                
				//dbCommandWrapper.AddInParameter("@officeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@OrderBy", DbType.AnsiString, orderBy.ToString());
				dbCommandWrapper.AddInParameter("@OrderDirection", DbType.AnsiString, orderDirection.ToString());
				dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
				dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
				dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		
		public virtual IDataReader GetBulkOrders(string supplierCode, int type, int sortType, int page, int pageSize)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersGetListBySortType");
                
				dbCommandWrapper.AddInParameter("@supplier", DbType.AnsiString, supplierCode);	
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);				
				dbCommandWrapper.AddInParameter("@SortType", DbType.Int32, sortType);				
				dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
				dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
				dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}

		public virtual int CheckUniqueBONo(int owner, string BONo)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersCheckUniqueBONo");
                
				dbCommandWrapper.AddInParameter("@owner", DbType.Int32, owner);
				dbCommandWrapper.AddInParameter("@BONo", DbType.AnsiString, BONo);
                
				return Convert.ToInt32(DataAccessHelper.ExecuteScalar(database, dbCommandWrapper));
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrderException, ex);
			}
		}

		public virtual IDataReader GetProductList()
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersGetProductList");            			

				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetBulkOrdersException, ex);
			}
		}
		#region "override methods"		
		// overwrite to set null values		
		public override void CreateBulkOrder(BulkOrder bulkOrder)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersInsert");
                
				dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, bulkOrder.OrderDate);
				dbCommandWrapper.AddInParameter("@Type", DbType.Byte, bulkOrder.Type);
				dbCommandWrapper.AddInParameter("@ProductCode", DbType.AnsiString, bulkOrder.ProductCode);				
				dbCommandWrapper.AddInParameter("@Description", DbType.String, bulkOrder.Description);
				dbCommandWrapper.AddInParameter("@Color", DbType.String, bulkOrder.Color);
				dbCommandWrapper.AddInParameter("@StartDate", DbType.DateTime, bulkOrder.StartDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.StartDate);
				dbCommandWrapper.AddInParameter("@TotalQtyOrdered", DbType.Int32, bulkOrder.TotalQtyOrdered);
				dbCommandWrapper.AddInParameter("@TotalQtyCompleted", DbType.Int32, bulkOrder.TotalQtyCompleted==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyCompleted);
				dbCommandWrapper.AddInParameter("@TotalQtyInProgressBelow50P", DbType.Int32, bulkOrder.TotalQtyInProgressBelow50P==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyInProgressBelow50P);
				dbCommandWrapper.AddInParameter("@TotalQtyInProgressAbove50P", DbType.Int32, bulkOrder.TotalQtyInProgressAbove50P==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyInProgressAbove50P);
				dbCommandWrapper.AddInParameter("@EstimatedDate", DbType.DateTime, bulkOrder.EstimatedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.EstimatedDate);
				dbCommandWrapper.AddInParameter("@StockLastCheckedDate", DbType.DateTime, bulkOrder.StockLastCheckedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.StockLastCheckedDate);
				dbCommandWrapper.AddInParameter("@StockLastCheckedQty", DbType.Int32, bulkOrder.StockLastCheckedQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.StockLastCheckedQty);
				dbCommandWrapper.AddInParameter("@FOB", DbType.Currency, bulkOrder.FOB==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.FOB);				
				dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, bulkOrder.CompletedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.CompletedDate);
				dbCommandWrapper.AddInParameter("@SupplierCode", DbType.AnsiString, bulkOrder.SupplierCode);
				dbCommandWrapper.AddInParameter("@Owner", DbType.Int32, bulkOrder.Owner);
				dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, bulkOrder.Catalogue);
				dbCommandWrapper.AddInParameter("@Page", DbType.AnsiString, bulkOrder.Page);
				dbCommandWrapper.AddInParameter("@Pack", DbType.AnsiString, bulkOrder.Pack);
				dbCommandWrapper.AddInParameter("@PackQty", DbType.Int32, bulkOrder.PackQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.PackQty);
				dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, bulkOrder.CatalogID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.CatalogID);
				dbCommandWrapper.AddInParameter("@Target", DbType.Boolean, bulkOrder.Target);
				dbCommandWrapper.AddInParameter("@SalesDescription1", DbType.String, bulkOrder.SalesDescription1);
				dbCommandWrapper.AddInParameter("@SalesDescription2", DbType.String, bulkOrder.SalesDescription2);
				dbCommandWrapper.AddInParameter("@StockLocation", DbType.AnsiString, bulkOrder.StockLocation);
                dbCommandWrapper.AddInParameter("@AvailableInUSA", DbType.Boolean, bulkOrder.AvailableInUSA);
				dbCommandWrapper.AddInParameter("@BONo", DbType.AnsiString, bulkOrder.BONo);
				dbCommandWrapper.AddInParameter("@MinOrderQtyValue", DbType.Int32, bulkOrder.MinOrderQtyValue==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.MinOrderQtyValue);
				dbCommandWrapper.AddInParameter("@MinOrderQty", DbType.AnsiString, bulkOrder.MinOrderQty);
				dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Currency, bulkOrder.MinQtyVolume==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.MinQtyVolume);
				dbCommandWrapper.AddInParameter("@TallPaletQty", DbType.AnsiString, bulkOrder.TallPaletQty);				
				dbCommandWrapper.AddInParameter("@UnitSettingID", DbType.Int32, bulkOrder.UnitSettingID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.UnitSettingID);				
				dbCommandWrapper.AddInParameter("@IDSSortID", DbType.Int32, bulkOrder.IDSSortID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.IDSSortID);				
                dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, bulkOrder.IsArchived);
				dbCommandWrapper.AddInParameter("@CatPackDesc", DbType.AnsiString, bulkOrder.CatPackDesc);
				dbCommandWrapper.AddInParameter("@MUKTHGCatPackQty", DbType.Int32, bulkOrder.MUKTHGCatPackQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.MUKTHGCatPackQty);
				dbCommandWrapper.AddInParameter("@MUKTHGCatPackDesc", DbType.AnsiString, bulkOrder.MUKTHGCatPackDesc);
				dbCommandWrapper.AddInParameter("@HomeDelivered15Price", DbType.Currency, bulkOrder.HomeDelivered15Price==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.HomeDelivered15Price);
				dbCommandWrapper.AddInParameter("@CurrencyID", DbType.Int32, bulkOrder.CurrencyID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.CurrencyID);
				 dbCommandWrapper.AddInParameter("@IsShownInLGDCatalog", DbType.Boolean, bulkOrder.IsShownInLGDCatalog);
				dbCommandWrapper.AddInParameter("@SpecialOfferDisc", DbType.Currency, bulkOrder.SpecialOfferDisc==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.SpecialOfferDisc);
				dbCommandWrapper.AddInParameter("@QtyUnitsPer40Cont", DbType.Int32, bulkOrder.QtyUnitsPer40Cont==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.QtyUnitsPer40Cont);
				dbCommandWrapper.AddInParameter("@QtyUnitsPer20Cont", DbType.Int32,bulkOrder.QtyUnitsPer20Cont==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.QtyUnitsPer20Cont);
				dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Currency, bulkOrder.UnitWeight==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.UnitWeight);
				dbCommandWrapper.AddInParameter("@HCC_Code", DbType.AnsiString,  bulkOrder.HCC_Code == string.Empty?DBNull.Value:(object)bulkOrder.HCC_Code);
				dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, bulkOrder.ModifiedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.ModifiedDate);
				dbCommandWrapper.AddInParameter("@UnitsPerOuter", DbType.Int32, bulkOrder.UnitsPerOuter==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.UnitsPerOuter);
				dbCommandWrapper.AddInParameter("@UnitFOBPrice", DbType.Currency, bulkOrder.UnitFOBPrice==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.UnitFOBPrice);

				dbCommandWrapper.AddOutParameter("@BulkOrderID", DbType.Int32, 0);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
				bulkOrder.BulkOrderID = (int)dbCommandWrapper.GetParameterValue("@BulkOrderID");
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateBulkOrderException, ex);
			}
		}

		public override void UpdateBulkOrder(BulkOrder bulkOrder)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spBulkOrdersUpdate");            
                
				dbCommandWrapper.AddInParameter("@BulkOrderID", DbType.Int32, bulkOrder.BulkOrderID);
				dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, bulkOrder.OrderDate);
				dbCommandWrapper.AddInParameter("@Type", DbType.Byte, bulkOrder.Type);
				dbCommandWrapper.AddInParameter("@ProductCode", DbType.AnsiString, bulkOrder.ProductCode);				
				dbCommandWrapper.AddInParameter("@Description", DbType.String, bulkOrder.Description);
				dbCommandWrapper.AddInParameter("@Color", DbType.String, bulkOrder.Color);
				dbCommandWrapper.AddInParameter("@StartDate", DbType.DateTime, bulkOrder.StartDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.StartDate);
				dbCommandWrapper.AddInParameter("@TotalQtyOrdered", DbType.Int32, bulkOrder.TotalQtyOrdered);
				dbCommandWrapper.AddInParameter("@TotalQtyCompleted", DbType.Int32, bulkOrder.TotalQtyCompleted==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyCompleted);
				dbCommandWrapper.AddInParameter("@TotalQtyInProgressBelow50P", DbType.Int32, bulkOrder.TotalQtyInProgressBelow50P==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyInProgressBelow50P);
				dbCommandWrapper.AddInParameter("@TotalQtyInProgressAbove50P", DbType.Int32, bulkOrder.TotalQtyInProgressAbove50P==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.TotalQtyInProgressAbove50P);
				dbCommandWrapper.AddInParameter("@EstimatedDate", DbType.DateTime, bulkOrder.EstimatedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.EstimatedDate);
				dbCommandWrapper.AddInParameter("@StockLastCheckedDate", DbType.DateTime, bulkOrder.StockLastCheckedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.StockLastCheckedDate);
				dbCommandWrapper.AddInParameter("@StockLastCheckedQty", DbType.Int32, bulkOrder.StockLastCheckedQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.StockLastCheckedQty);
				dbCommandWrapper.AddInParameter("@FOB", DbType.Currency, bulkOrder.FOB==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.FOB);				
				dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, bulkOrder.CompletedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.CompletedDate);
				 dbCommandWrapper.AddInParameter("@Owner", DbType.Int32, bulkOrder.Owner);
				dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, bulkOrder.Catalogue);
				dbCommandWrapper.AddInParameter("@Page", DbType.AnsiString, bulkOrder.Page);
				dbCommandWrapper.AddInParameter("@Pack", DbType.AnsiString, bulkOrder.Pack);
				dbCommandWrapper.AddInParameter("@PackQty", DbType.Int32, bulkOrder.PackQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.PackQty);
				dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, bulkOrder.CatalogID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.CatalogID);
				dbCommandWrapper.AddInParameter("@Target", DbType.Boolean, bulkOrder.Target);
				dbCommandWrapper.AddInParameter("@SalesDescription1", DbType.String, bulkOrder.SalesDescription1);
				dbCommandWrapper.AddInParameter("@SalesDescription2", DbType.String, bulkOrder.SalesDescription2);              
				dbCommandWrapper.AddInParameter("@SupplierCode", DbType.AnsiString, bulkOrder.SupplierCode);
				dbCommandWrapper.AddInParameter("@StockLocation", DbType.AnsiString, bulkOrder.StockLocation);
                dbCommandWrapper.AddInParameter("@AvailableInUSA", DbType.Boolean, bulkOrder.AvailableInUSA);
				dbCommandWrapper.AddInParameter("@BONo", DbType.AnsiString, bulkOrder.BONo);
				dbCommandWrapper.AddInParameter("@MinOrderQtyValue", DbType.Int32, bulkOrder.MinOrderQtyValue==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.MinOrderQtyValue);
				dbCommandWrapper.AddInParameter("@MinOrderQty", DbType.AnsiString, bulkOrder.MinOrderQty);
				dbCommandWrapper.AddInParameter("@MinQtyVolume", DbType.Currency, bulkOrder.MinQtyVolume==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.MinQtyVolume);
				dbCommandWrapper.AddInParameter("@TallPaletQty", DbType.AnsiString, bulkOrder.TallPaletQty);
				dbCommandWrapper.AddInParameter("@UnitSettingID",  DbType.Int32, bulkOrder.UnitSettingID == Nulls.NullInteger? DBNull.Value: (object)bulkOrder.UnitSettingID);				
                dbCommandWrapper.AddInParameter("@IDSSortID", DbType.Int32, bulkOrder.IDSSortID ==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.IDSSortID);				
				dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, bulkOrder.IsArchived);
				dbCommandWrapper.AddInParameter("@CatPackDesc", DbType.AnsiString, bulkOrder.CatPackDesc);
				dbCommandWrapper.AddInParameter("@MUKTHGCatPackQty", DbType.Int32, bulkOrder.MUKTHGCatPackQty==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.MUKTHGCatPackQty);
				dbCommandWrapper.AddInParameter("@MUKTHGCatPackDesc", DbType.AnsiString, bulkOrder.MUKTHGCatPackDesc);
				dbCommandWrapper.AddInParameter("@HomeDelivered15Price", DbType.Currency, bulkOrder.HomeDelivered15Price==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.HomeDelivered15Price);
				dbCommandWrapper.AddInParameter("@CurrencyID", DbType.Int32, bulkOrder.CurrencyID==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.CurrencyID);
				dbCommandWrapper.AddInParameter("@IsShownInLGDCatalog", DbType.Boolean, bulkOrder.IsShownInLGDCatalog);
				dbCommandWrapper.AddInParameter("@SpecialOfferDisc", DbType.Currency, bulkOrder.SpecialOfferDisc==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.SpecialOfferDisc);
				dbCommandWrapper.AddInParameter("@QtyUnitsPer40Cont", DbType.Int32, bulkOrder.QtyUnitsPer40Cont==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.QtyUnitsPer40Cont);
				dbCommandWrapper.AddInParameter("@QtyUnitsPer20Cont", DbType.Int32,bulkOrder.QtyUnitsPer20Cont==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.QtyUnitsPer20Cont);
				dbCommandWrapper.AddInParameter("@UnitWeight", DbType.Currency, bulkOrder.UnitWeight==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.UnitWeight);
				dbCommandWrapper.AddInParameter("@HCC_Code", DbType.AnsiString,  bulkOrder.HCC_Code == string.Empty?DBNull.Value:(object)bulkOrder.HCC_Code);
				dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, bulkOrder.ModifiedDate==Nulls.NullDate?DBNull.Value:(object)bulkOrder.ModifiedDate);
				dbCommandWrapper.AddInParameter("@UnitsPerOuter", DbType.Int32, bulkOrder.UnitsPerOuter==Nulls.NullInteger?DBNull.Value:(object)bulkOrder.UnitsPerOuter);
				dbCommandWrapper.AddInParameter("@UnitFOBPrice", DbType.Currency, bulkOrder.UnitFOBPrice==Nulls.NullDecimal?DBNull.Value:(object)bulkOrder.UnitFOBPrice);
			
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessUpdateBulkOrderException, ex);
			}
		}
		#endregion

	}
}
